﻿using Microsoft.Data.Sqlite;

namespace StudentManagementSystem.Data
{
    public class DatabaseHelper
    {
        private readonly string _connectionString;

        public DatabaseHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        #region 数据库初始化
        public void InitializeDatabase()
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            // 创建学校表
            var command = connection.CreateCommand();
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Schools (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Address TEXT
                )";
            command.ExecuteNonQuery();

            // 创建班级表
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Classes (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    SchoolId INTEGER,
                    FOREIGN KEY (SchoolId) REFERENCES Schools(Id) ON DELETE CASCADE
                )";
            command.ExecuteNonQuery();

            // 创建学生表
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Students (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER,
                    Gender TEXT,
                    ClassId INTEGER,
                    FOREIGN KEY (ClassId) REFERENCES Classes(Id) ON DELETE CASCADE
                )";
            command.ExecuteNonQuery();

            // 创建日志表
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Logs (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Operation TEXT NOT NULL,
                    EntityType TEXT NOT NULL,
                    EntityId INTEGER,
                    OperationTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                    Operator TEXT
                )";
            command.ExecuteNonQuery();
        }
        #endregion

        #region 学校操作
        public int AddSchool(School school)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO Schools (Name, Address) 
                VALUES (@Name, @Address);
                SELECT last_insert_rowid();";

            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address ?? (object)DBNull.Value);

            var id = Convert.ToInt32(command.ExecuteScalar());

            LogOperation("Add", "School", id, "System");

            return id;
        }

        public School GetSchoolById(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Schools WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                return new School
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Address = reader["Address"]?.ToString()
                };
            }

            return null;
        }

        public List<School> GetAllSchools()
        {
            var schools = new List<School>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Schools ORDER BY Name";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                schools.Add(new School
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Address = reader["Address"]?.ToString()
                });
            }

            return schools;
        }

        public void UpdateSchool(School school)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE Schools 
                SET Name = @Name, Address = @Address
                WHERE Id = @Id";

            command.Parameters.AddWithValue("@Id", school.Id);
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address ?? (object)DBNull.Value);

            command.ExecuteNonQuery();

            LogOperation("Update", "School", school.Id, "System");
        }

        public void DeleteSchool(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Schools WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();

            LogOperation("Delete", "School", id, "System");
        }
        #endregion

        #region 班级操作
        public int AddClass(Class cls)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO Classes (Name, SchoolId) 
                VALUES (@Name, @SchoolId);
                SELECT last_insert_rowid();";

            command.Parameters.AddWithValue("@Name", cls.Name);
            command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);

            var id = Convert.ToInt32(command.ExecuteScalar());

            LogOperation("Add", "Class", id, "System");

            return id;
        }

        public List<Class> GetAllClassesWithSchool()
        {
            var classes = new List<Class>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT c.*, s.Name as SchoolName 
                FROM Classes c
                LEFT JOIN Schools s ON c.SchoolId = s.Id
                ORDER BY c.Name";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                classes.Add(new Class
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    SchoolId = Convert.ToInt32(reader["SchoolId"]),
                    School = new School { Name = reader["SchoolName"]?.ToString() }
                });
            }

            return classes;
        }

        public Class GetClassById(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT c.*, s.Name as SchoolName 
                FROM Classes c
                LEFT JOIN Schools s ON c.SchoolId = s.Id
                WHERE c.Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                return new Class
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    SchoolId = Convert.ToInt32(reader["SchoolId"]),
                    School = new School { Name = reader["SchoolName"]?.ToString() }
                };
            }

            return null;
        }

        public List<Class> GetClassesBySchoolId(int schoolId)
        {
            var classes = new List<Class>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Classes WHERE SchoolId = @SchoolId";
            command.Parameters.AddWithValue("@SchoolId", schoolId);

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                classes.Add(new Class
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    SchoolId = Convert.ToInt32(reader["SchoolId"])
                });
            }

            return classes;
        }

        public void UpdateClass(Class cls)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE Classes 
                SET Name = @Name, SchoolId = @SchoolId
                WHERE Id = @Id";

            command.Parameters.AddWithValue("@Id", cls.Id);
            command.Parameters.AddWithValue("@Name", cls.Name);
            command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);

            command.ExecuteNonQuery();

            LogOperation("Update", "Class", cls.Id, "System");
        }

        public void DeleteClass(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Classes WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();

            LogOperation("Delete", "Class", id, "System");
        }
        #endregion

        #region 学生操作
        public int AddStudent(Student student)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO Students (Name, Age, Gender, ClassId) 
                VALUES (@Name, @Age, @Gender, @ClassId);
                SELECT last_insert_rowid();";

            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@Gender", student.Gender);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            var id = Convert.ToInt32(command.ExecuteScalar());

            LogOperation("Add", "Student", id, "System");

            return id;
        }

        public List<Student> GetAllStudentsWithClass()
        {
            var students = new List<Student>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT s.*, c.Name as ClassName, sc.Name as SchoolName
                FROM Students s
                LEFT JOIN Classes c ON s.ClassId = c.Id
                LEFT JOIN Schools sc ON c.SchoolId = sc.Id
                ORDER BY s.Name";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                students.Add(new Student
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Age = Convert.ToInt32(reader["Age"]),
                    Gender = reader["Gender"].ToString(),
                    ClassId = Convert.ToInt32(reader["ClassId"]),
                    Class = new Class
                    {
                        Name = reader["ClassName"].ToString(),
                        School = new School { Name = reader["SchoolName"]?.ToString() }
                    }
                });
            }

            return students;
        }

        public Student GetStudentById(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT s.*, c.Name as ClassName, sc.Name as SchoolName
                FROM Students s
                LEFT JOIN Classes c ON s.ClassId = c.Id
                LEFT JOIN Schools sc ON c.SchoolId = sc.Id
                WHERE s.Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                return new Student
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Age = Convert.ToInt32(reader["Age"]),
                    Gender = reader["Gender"].ToString(),
                    ClassId = Convert.ToInt32(reader["ClassId"]),
                    Class = new Class
                    {
                        Name = reader["ClassName"].ToString(),
                        School = new School { Name = reader["SchoolName"]?.ToString() }
                    }
                };
            }

            return null;
        }

        public List<Student> GetStudentsByClassId(int classId)
        {
            var students = new List<Student>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Students WHERE ClassId = @ClassId";
            command.Parameters.AddWithValue("@ClassId", classId);

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                students.Add(new Student
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Age = Convert.ToInt32(reader["Age"]),
                    Gender = reader["Gender"].ToString(),
                    ClassId = Convert.ToInt32(reader["ClassId"])
                });
            }

            return students;
        }

        public void UpdateStudent(Student student)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE Students 
                SET Name = @Name, Age = @Age, Gender = @Gender, ClassId = @ClassId
                WHERE Id = @Id";

            command.Parameters.AddWithValue("@Id", student.Id);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@Gender", student.Gender);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            command.ExecuteNonQuery();

            LogOperation("Update", "Student", student.Id, "System");
        }

        public void DeleteStudent(int id)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Students WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();

            LogOperation("Delete", "Student", id, "System");
        }
        #endregion

        #region 日志操作
        public List<Log> GetAllLogs()
        {
            var logs = new List<Log>();

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Logs ORDER BY OperationTime DESC";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                logs.Add(new Log
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Operation = reader["Operation"].ToString(),
                    EntityType = reader["EntityType"].ToString(),
                    EntityId = Convert.ToInt32(reader["EntityId"]),
                    OperationTime = Convert.ToDateTime(reader["OperationTime"]),
                    Operator = reader["Operator"].ToString()
                });
            }

            return logs;
        }

        private void LogOperation(string operation, string entityType, int entityId, string operatorName)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO Logs (Operation, EntityType, EntityId, Operator)
                VALUES (@Operation, @EntityType, @EntityId, @Operator)";

            command.Parameters.AddWithValue("@Operation", operation);
            command.Parameters.AddWithValue("@EntityType", entityType);
            command.Parameters.AddWithValue("@EntityId", entityId);
            command.Parameters.AddWithValue("@Operator", operatorName);

            command.ExecuteNonQuery();
        }
        #endregion
    }
}